package intranet.models;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class CommentModel {
	Connection DBConnection=null;
	public CommentModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	
	/*--- WALL ---*/
		/* SELECT WALL BY ID */
	public ResultSet SelectWall(int wall_id){
		ResultSet result=null;
		String Conditional_String=(wall_id!=0)?" WHERE WALL_ID="+wall_id:"";
		try{
			Statement ConStatement=DBConnection.createStatement(
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_UPDATABLE);
			String Sql="SELECT * FROM WALLS "+Conditional_String+" ORDER BY WALL_ID DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/*--- WALL ITEM ---*/
	/* SELECT WALL BY ID */
public ResultSet SelectWallItem(int wall_id){
	ResultSet result=null;
	String Conditional_String=(wall_id!=0)?" WHERE I.WALL_ID="+wall_id:"";
	try{
		Statement ConStatement=DBConnection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
		String Sql="SELECT W.WALL_ID,W.TITLE,W.PURPOSE,I.ITEM_ID,I.ITEM_DESC,I.USER_POST,U.USER_NAME,TO_CHAR(I.DATE_POST,'DD/MM/YYYY') AS DATE_POST FROM (WALLS W INNER JOIN WALL_ITEMS I ON W.WALL_ID=I.WALL_ID) INNER JOIN USER_ACCOUNTS U ON U.USER_ID=I.USER_POST WHERE W.WALL_ID="+wall_id+" ORDER BY I.ITEM_ID DESC";
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}

	/* INSERT WALL TOPIC */
public void InsertNewItem(int wall_id,String item_desc,int userid){
	ResultSet result=null;
	try{
		Statement stm=DBConnection.createStatement();
		String sql="INSERT INTO WALL_ITEMS(ITEM_ID,WALL_ID,ITEM_DESC,USER_POST,DATE_POST) ";
		sql+="VALUES(ITEM_ID.NEXTVAL,"+wall_id+",'"+item_desc+"',"+userid+",SYSDATE)";
		result=stm.executeQuery(sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
}

	
	public ResultSet SelectComment(int item_id){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement(
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_UPDATABLE);
			String Sql="SELECT I.ITEM_ID,I.ITEM_DESC,C.COMMENT_ID,C.COMMENT_DESC,TO_CHAR(C.DATE_POST,'MM/DD/YYYY HH12:MI:SS AM') AS DATE_POST,C.USER_POST FROM ITEM_COMMENTS C INNER JOIN WALL_ITEMS I ON C.ITEM_ID=I.ITEM_ID WHERE C.ITEM_ID="+item_id+" ORDER BY C.COMMENT_ID ASC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	public int getMaxIdbyItemId(int item_id){
		int result=0;
		ResultSet rst=null;
		try{
			Statement ConStatement=DBConnection.createStatement(
	                ResultSet.TYPE_SCROLL_INSENSITIVE,
	                ResultSet.CONCUR_UPDATABLE);
			String Sql="SELECT MAX(COMMENT_ID) AS COMMENT_ID FROM ITEM_COMMENTS WHERE ITEM_ID="+item_id;
			rst=ConStatement.executeQuery(Sql);
			while(rst.next()){
				result=rst.getInt("COMMENT_ID");
			}
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	
	/* INSERT ITEM COMMENTS */
	public void InsertComment(int item_id,String comment_desc,int userid,String curDate){
		ResultSet result=null;
		try{
			Statement stm=DBConnection.createStatement();
			String sql="INSERT INTO ITEM_COMMENTS(COMMENT_ID,ITEM_ID,COMMENT_DESC,USER_POST,DATE_POST) VALUES(COMMENT_ID.NEXTVAL,"+item_id+",'"+comment_desc+"',"+userid+",'"+curDate+"')";
			//String sql="INSERT INTO ITEM_COMMENTS(COMMENT_ID,ITEM_ID,COMMENT_DESC,USER_POST) VALUES(555,22,'"+comment_desc+"',111)";
			stm.executeQuery(sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	public void DeleteComment(int id,int user_id){
		ResultSet result=null;
		try{
			Statement stm=DBConnection.createStatement();
			String sql="DELETE FROM ITEM_COMMENTS WHERE USER_POST="+user_id+" AND COMMENT_ID="+id;
			//String sql="INSERT INTO ITEM_COMMENTS(COMMENT_ID,ITEM_ID,COMMENT_DESC,USER_POST) VALUES(555,22,'"+comment_desc+"',111)";
			stm.executeQuery(sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
	}
	
	/* SELECT USER BY ID */
public ResultSet SelectUser(int user_id){
	ResultSet result=null;
	try{
		Statement ConStatement=DBConnection.createStatement();
		String Sql="SELECT * FROM USER_ACCOUNTS WHERE USER_ID="+user_id;
		result=ConStatement.executeQuery(Sql);
	}catch(Exception e){
		e.getLocalizedMessage();
	}
	return result;
}
}
